﻿Imports System.Xml
Imports System.Reflection
Imports Mysoft.Map.Workflow.Types
Imports Mysoft.Map.Workflow.BusinessDomain
Imports Mysoft.Map.Data
Imports System.Web
Imports System.Data
Imports System.Data.SqlClient


Public Class ProceedPlanAdjustSP_HTML

	Inherits BusinessDomainBase

	Private _businessGUID As String		'业务GUID
	Private _xml As XmlDocument = New XmlDocument

	Public Sub New()
		MyBase.New("/PubProject/workflow/qmys/ProceedPlanAdjustSP_HTML.xml")
		_xml.LoadXml(MyBase.XML)
	End Sub

	'属性：业务GUID
	Public Overrides Property BusinessGUID() As String
		Get
			Return _businessGUID
		End Get
		Set(ByVal Value As String)
			_businessGUID = Value
		End Set
	End Property

	'属性：XML
	'最终XML格式：

	Public Overrides Property XML() As String
		Get
			Return _xml.OuterXml
		End Get
		Set(ByVal Value As String)
			_xml.LoadXml(Value)
		End Set
	End Property

	Public Overrides Function Initiate() As String
		Dim strSql, strDeptAdjustDtlGUID, strApproveState, strDeptGUID, strYear As String
		Dim decPlanAmount, decBalanceAmount, decAdjustedAmount As Decimal
		Dim sbSql As System.Text.StringBuilder = New System.Text.StringBuilder()

		Dim group As XmlNode = _xml.SelectSingleNode("/BusinessType/Item/Group[@name='调整明细列表']")
		Dim list As XmlNodeList = group.SelectNodes("/Item")

		strSql = "SELECT ApproveState, Year, DeptGUID FROM ys_DeptAdjust WHERE DeptAdjustGUID='" & Me._businessGUID & "'"
		Dim dt As DataTable = MyDB.GetDataTable(strSql)

		If dt.Rows.Count > 0 Then
			strApproveState = dt.Rows(0).Item("ApproveState")
			strDeptGUID = dt.Rows(0).Item("Year")
			strYear = dt.Rows(0).Item("Year")

			dt = GetDeptAdjustDtlInfo(Me._businessGUID, strApproveState, strDeptGUID, strYear)

			For i As Integer = 0 To dt.Rows.Count - 1
				strDeptAdjustDtlGUID = dt.Rows.Item("DeptAdjustDtlGUID").ToString
				decPlanAmount = Decimal.Parse(dt.Rows(i).Item("PlanAmount").ToString)
				decBalanceAmount = Decimal.Parse(dt.Rows(i).Item("BalanceAmount").ToString)
				decAdjustedAmount = Decimal.Parse(dt.Rows(i).Item("AdjustedAmount").ToString)

				sbSql.AppendLine(String.Format("UPDATE ys_DeptAdjustDtl SET PlanAmount = {1}, BalanceAmount = {2}, AdjustedAmount = {3} WHERE DeptAdjustDtlGUID = '{0}';", strDeptAdjustDtlGUID, decPlanAmount, decBalanceAmount, decAdjustedAmount))
			Next

		End If

		sbSql.AppendLine("UPDATE ys_DeptAdjust SET ApproveState='审核中' WHERE DeptAdjustGUID='" & Me._businessGUID & "';")

		'提交更新语句
		If MyDB.ExecSQL(sbSql.ToString()) < 0 Then
			Return "<xml result=""false"" errormessage=""审批失败，请稍后再试！""></xml>"
		End If

		'返回执行是否成功的信息
		Return "<xml result=""true"" errormessage=""""></xml>"
	End Function
	'功能：取业务数据，填充XML
	Public Overrides Function LoadData() As Integer
		Dim strSQL As String
		Dim dtTemp As DataTable
		Dim strApproveState As String = "未审核"
		Dim nYear As Integer = 0
		Dim strDeptGUID As String = Guid.NewGuid().ToString

		'主记录    
		strSQL = "SELECT DeptAdjustGUID,Subject,DeptGUID,vys_myBusinessUnit.BUName,Adjuster,AdjustDate,AdjustReason,ys_DeptAdjust.adjustamount, ApproveState, Year, DeptGUID" & _
		" FROM ys_DeptAdjust left join vys_myBusinessUnit on ys_DeptAdjust.deptguid=vys_myBusinessUnit.buguid " & _
		" WHERE DeptAdjustGUID='" & Me._businessGUID & "'"

		dtTemp = MyDB.GetDataTable(strSQL)

		If dtTemp.Rows.Count > 0 Then
			'根节点业务GUID属性
			_xml.SelectSingleNode("/BusinessType").Attributes.GetNamedItem("BusinessGUID").Value = Me._businessGUID

			_xml.SelectSingleNode("/BusinessType/Item/Domain[@name='预算调整单据GUID']").InnerText = dtTemp.Rows(0).Item("DeptAdjustGUID").ToString
			_xml.SelectSingleNode("/BusinessType/Item/Domain[@name='部门GUID']").InnerText = dtTemp.Rows(0).Item("DeptGUID").ToString
			_xml.SelectSingleNode("/BusinessType/Item/Domain[@name='调整部门']").InnerText = dtTemp.Rows(0).Item("BUName").ToString
			_xml.SelectSingleNode("/BusinessType/Item/Domain[@name='调整主题']").InnerText = dtTemp.Rows(0).Item("Subject").ToString
			_xml.SelectSingleNode("/BusinessType/Item/Domain[@name='调整人']").InnerText = dtTemp.Rows(0).Item("Adjuster").ToString
			_xml.SelectSingleNode("/BusinessType/Item/Domain[@name='调整日期']").InnerText = Format(dtTemp.Rows(0).Item("AdjustDate"), "yyyy-MM-dd")
			_xml.SelectSingleNode("/BusinessType/Item/Domain[@name='调整原因']").InnerText = dtTemp.Rows(0).Item("AdjustReason").ToString
			_xml.SelectSingleNode("/BusinessType/Item/Domain[@name='调整金额']").InnerText = Format(dtTemp.Rows(0).Item("adjustamount"), "#,##0.00")

			strApproveState = dtTemp.Rows(0).Item("ApproveState").ToString
			nYear = Integer.Parse(dtTemp.Rows(0).Item("Year").ToString)
			strDeptGUID = dtTemp.Rows(0).Item("DeptGUID").ToString
		End If

		'获取“部门预算”列表的数据
		strSQL = "Exec usp_ys_GetYearPlanForProceeding '" & Me._businessGUID & "'"
		dtTemp = MyDB.GetDataTable(strSQL)
		If dtTemp.Rows.Count > 0 Then
			Dim group As XmlNode = _xml.SelectSingleNode("/BusinessType/Item/Group[@name='部门预算']")
			'删除分组下的所有节点
			Dim item As XmlNode = group.SelectSingleNode("Item")

			group.RemoveChild(item)

			For i As Integer = 0 To dtTemp.Rows.Count - 1

				Dim newXmlNode As XmlElement = item.Clone()

				newXmlNode.SelectSingleNode("Domain[@name='部门费用名称']").InnerText = dtTemp.Rows(i).Item("DeptName").ToString
				newXmlNode.SelectSingleNode("Domain[@name='年初预算']").InnerText = Format(dtTemp.Rows(i).Item("PlanAmount"), "#,##0.00")
				newXmlNode.SelectSingleNode("Domain[@name='累计调整']").InnerText = Format(dtTemp.Rows(i).Item("AdjustAmount"), "#,##0.00")
				newXmlNode.SelectSingleNode("Domain[@name='本次调整']").InnerText = Format(dtTemp.Rows(i).Item("CurAdjustAmount"), "#,##0.00")
				newXmlNode.SelectSingleNode("Domain[@name='本次调整后']").InnerText = Format(dtTemp.Rows(i).Item("AdjustedAmount"), "#,##0.00")

				group.AppendChild(newXmlNode)
			Next
		End If

		'获取“调整明细”列表的数据
		dtTemp = GetDeptAdjustDtlInfo(Me._businessGUID, strApproveState, strDeptGUID, nYear)

		If dtTemp.Rows.Count > 0 Then
			Dim group As XmlNode = _xml.SelectSingleNode("/BusinessType/Item/Group[@name='调整明细列表']")
			'删除分组下的所有节点
			Dim item As XmlNode = group.SelectSingleNode("Item")

			group.RemoveChild(item)

			For i As Integer = 0 To dtTemp.Rows.Count - 1

				Dim newXmlNode As XmlElement = item.Clone()

				newXmlNode.SelectSingleNode("Domain[@name='序号']").InnerText = dtTemp.Rows(i).Item("DeptAdjustDtlGUID").ToString
				newXmlNode.SelectSingleNode("Domain[@name='序号']").InnerText = i + 1
				newXmlNode.SelectSingleNode("Domain[@name='事项名称']").InnerText = dtTemp.Rows(i).Item("ProceedingName").ToString
				newXmlNode.SelectSingleNode("Domain[@name='科目名称']").InnerText = dtTemp.Rows(i).Item("CostShortName").ToString
				newXmlNode.SelectSingleNode("Domain[@name='月份']").InnerText = dtTemp.Rows(i).Item("Month").ToString
				newXmlNode.SelectSingleNode("Domain[@name='预算']").InnerText = Format(dtTemp.Rows(i).Item("PlanAmount"), "#,##0.00")
				newXmlNode.SelectSingleNode("Domain[@name='余额']").InnerText = Format(dtTemp.Rows(i).Item("BalanceAmount"), "#,##0.00")
				newXmlNode.SelectSingleNode("Domain[@name='调整']").InnerText = Format(dtTemp.Rows(i).Item("AdjustAmount"), "#,##0.00")
				newXmlNode.SelectSingleNode("Domain[@name='调整后']").InnerText = Format(dtTemp.Rows(i).Item("AdjustedAmount"), "#,##0.00")
				newXmlNode.SelectSingleNode("Domain[@name='调整明细原因']").InnerText = dtTemp.Rows(i).Item("Remarks").ToString

				group.AppendChild(newXmlNode)
			Next
		End If

		Return 0
	End Function

	'功能：根据XML进行数据归档
	Public Overrides Function SaveData() As String
		Dim MyPublicMng As New PublicMNG
		'获取审核人、审核日期的审核抬头信息
		Dim strApproveSeparator As String = MyPublicMng.GetApproveSeparator("审批通过")
		Dim i As Integer
		Dim dtTemp As DataTable
		Dim strSQL As String = "UPDATE ys_DeptAdjust SET ApproveState='已审核'" & _
				",ApproveBy='" & HttpContext.Current.Session("UserName") & "'" & _
				",ApproveDate=GetDate()" & _
				",Remarks='" & strApproveSeparator & Chr(13) & Chr(10) & "    工作流审批通过" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "' + Convert(varchar(6000), IsNull(Remarks, '')) " & _
				" WHERE DeptAdjustGUID='" & Me._businessGUID & "';"
		Dim strSQLALL As New System.Text.StringBuilder
		If MyDB.ExecSQL(strSQL) < 0 Then
			Return "<xml result=""false"" errormessage=""审批失败，请稍后再试！""></xml>"
		End If
		strSQL = "SELECT ProceedingGUID,CostGUID,ys_DeptAdjustDtl.Year,Month,ys_DeptAdjustDtl.AdjustAmount,deptguid FROM ys_DeptAdjustDtl left join ys_DeptAdjust on ys_DeptAdjustDtl.DeptAdjustGUID=ys_DeptAdjust.DeptAdjustGUID WHERE ys_DeptAdjustDtl.DeptAdjustGUID='" & Me._businessGUID & "'"
		dtTemp = MyDB.GetDataTable(strSQL)

		Try
			For i = 0 To dtTemp.Rows.Count - 1
				'调用存储过程同步更新
				UpdateYearPlan("调整", dtTemp.Rows(i).Item("DeptGUID").ToString, dtTemp.Rows(i).Item("ProceedingGUID").ToString, dtTemp.Rows(i).Item("CostGUID").ToString, dtTemp.Rows(i).Item("Year").ToString, dtTemp.Rows(i).Item("Month").ToString, dtTemp.Rows(i).Item("AdjustAmount").ToString)
			Next
		Catch ex As Exception
			Return "<xml result=""false"" errormessage=""审批失败，请稍后再试！""></xml>"
		End Try

		Return "<xml result=""true"" errormessage=""""></xml>"
	End Function

	'功能：不通过处理
	Public Overrides Function SaveData_NoPass() As String
		Dim MyPublicMng As New PublicMNG
		'获取审核人、审核日期的审核抬头信息
		Dim strApproveSeparator As String = MyPublicMng.GetApproveSeparator("审批不通过")
		Dim strSQL As String = "UPDATE ys_DeptAdjust SET ApproveState='未审核' " & _
				",Remarks='" & strApproveSeparator & Chr(13) & Chr(10) & "    工作流审批不通过" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "' + Convert(varchar(6000), IsNull(Remarks, '')) " & _
				"WHERE DeptAdjustGUID='" & Me._businessGUID & "';"

		strSQL = strSQL & GetUpdateDeptAdjustDtlSQL(Me._businessGUID)

		If MyDB.ExecSQL(strSQL) < 0 Then
			Return "<xml result=""false"" errormessage=""审批失败，请稍后再试！""></xml>"
		End If

		Return "<xml result=""true"" errormessage=""""></xml>"

	End Function
	'功能：作废
	Public Overrides Function SaveData_Cancel() As String
		Dim MyPublicMng As New PublicMNG
		'获取审核人、审核日期的审核抬头信息
		Dim strApproveSeparator As String = MyPublicMng.GetApproveSeparator("审批作废")
		Dim strSQL As String = "UPDATE ys_DeptAdjust SET ApproveState='未审核' " & _
				",Remarks='" & strApproveSeparator & Chr(13) & Chr(10) & "    工作流审批作废" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "' + Convert(varchar(6000), IsNull(Remarks, '')) " & _
				" WHERE DeptAdjustGUID='" & Me._businessGUID & "';"

		strSQL = strSQL & GetUpdateDeptAdjustDtlSQL(Me._businessGUID)

		If MyDB.ExecSQL(strSQL) < 0 Then
			Return "<xml result=""false"" errormessage=""审批失败，请稍后再试！""></xml>"
		End If

		Return "<xml result=""true"" errormessage=""""></xml>"
	End Function

	'功能：审批通过后再驳回数据处理
	Public Function SaveData_RollBack() As String
		Dim MyPublicMng As New PublicMNG
		'获取审核人、审核日期的审核抬头信息
		Dim strApproveSeparator As String = MyPublicMng.GetApproveSeparator("审批驳回")
		Dim strSQL As String = "UPDATE ys_DeptAdjust SET ApproveState='未审核' " & _
				",Remarks='" & strApproveSeparator & Chr(13) & Chr(10) & "    工作流审批驳回" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "' + Convert(varchar(6000), IsNull(Remarks, '')) " & _
				" WHERE DeptAdjustGUID='" & Me._businessGUID & "';"

		strSQL = strSQL & GetUpdateDeptAdjustDtlSQL(Me._businessGUID)

		If MyDB.ExecSQL(strSQL) < 0 Then
			Return "<xml result=""false"" errormessage=""审批失败，请稍后再试！""></xml>"
		End If

		Return "<xml result=""true"" errormessage=""""></xml>"

	End Function

	'功能：发起撤回
	Public Overrides Function InitiateWithDrawn() As String
		Dim MyPublicMng As New PublicMNG
		'获取审核人、审核日期的审核抬头信息
		Dim strApproveSeparator As String = MyPublicMng.GetApproveSeparator("审批不通过")
		Dim strSQL As String = "UPDATE ys_DeptAdjust SET ApproveState='未审核' " & _
							   ",Remarks='" & strApproveSeparator & Chr(13) & Chr(10) & "    工作流审批不通过" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "' + Convert(varchar(6000), IsNull(Remarks, '')) " & _
							   " WHERE DeptAdjustGUID='" & Me._businessGUID & "';"

		strSQL = strSQL & GetUpdateDeptAdjustDtlSQL(Me._businessGUID)

		If MyDB.ExecSQL(strSQL) < 0 Then
			Return "<xml result=""false"" errormessage=""审批失败，请稍后再试！""></xml>"
		End If

		Return "<xml result=""true"" errormessage=""""></xml>"
	End Function

	Private Shared Sub UpdateYearPlan(ByVal strType As String, ByVal strDeptGUID As String, ByVal strProceedingGUID As String, ByVal strCostGUID As String, ByVal iYear As String, ByVal iMonth As String, ByVal decAdjustAmount As String)
		Dim myConnection As SqlConnection = New SqlConnection(MyDB.GetSqlConnectionString())
		Dim myCommand As SqlCommand = New SqlCommand("usp_Ys_UpdateYearPlanCost", myConnection)
		Dim MyAdapter As SqlDataAdapter
		Dim a_ds As New DataSet

		MyAdapter = New SqlDataAdapter
		myCommand.CommandType = CommandType.StoredProcedure
		MyAdapter.SelectCommand = myCommand

		Dim sqlParams As SqlParameterCollection = myCommand.Parameters

		sqlParams.Add(New SqlParameter("@Type", SqlDbType.NVarChar, 40))
		sqlParams.Add(New SqlParameter("@DeptGUID", SqlDbType.NVarChar, 40))
		sqlParams.Add(New SqlParameter("@ProceedingGUID", SqlDbType.NVarChar, 40))
		sqlParams.Add(New SqlParameter("@CostGUID", SqlDbType.NVarChar, 40))
		sqlParams.Add(New SqlParameter("@Year", SqlDbType.Int))
		sqlParams.Add(New SqlParameter("@Month", SqlDbType.Int))
		sqlParams.Add(New SqlParameter("@Amount", SqlDbType.Money))

		'参数赋值
		sqlParams("@Type").Value = strType
		sqlParams("@DeptGUID").Value = strDeptGUID
		sqlParams("@ProceedingGUID").Value = strProceedingGUID
		sqlParams("@CostGUID").Value = strCostGUID
		sqlParams("@Year").Value = iYear
		sqlParams("@Month").Value = iMonth
		sqlParams("@Amount").Value = decAdjustAmount

		Try
			MyAdapter.Fill(a_ds)

		Catch exc As Exception

		Finally
			myConnection.Close()
		End Try

	End Sub

	'获取“调整明细”列表的数据
	'参数说明：
	'strApproveState 部门费用调整单审批状态
	'strDeptGUID 部门费用调整单的部门GUID
	'nYear 部门费用调整单的年度
	Private Function GetDeptAdjustDtlInfo(ByVal strDeptAdjustGUID As String, ByVal strApproveState As String, ByVal strDeptGUID As String, ByVal nYear As String) As DataTable

		Dim strSQL As String = "SELECT '' as rowid,1 as entity,temp.DeptAdjustDtlGUID,temp.DeptAdjustGUID,temp.ProceedingGUID,temp.CostGUID,temp.Year,temp.Month,temp.AdjustAmount,temp.Remarks,c.ProceedingName,b.CostShortName, " & _
		  " CASE '" & strApproveState & "' WHEN '未审核' THEN temp.ssPlanAmount ELSE temp.PlanAmount END AS PlanAmount," & _
		  " CASE '" & strApproveState & "' WHEN '未审核' THEN temp.ssPlanAmount - temp.ssFactAmount ELSE temp.BalanceAmount END AS BalanceAmount," & _
		  " CASE '" & strApproveState & "' WHEN '未审核' THEN temp.ssPlanAmount + temp.AdjustAmount else temp.AdjustedAmount END AS AdjustedAmount" & _
		  " FROM (" & _
		  "SELECT a.DeptAdjustDtlGUID,a.DeptAdjustGUID,a.ProceedingGUID,a.CostGUID,a.Year,a.Month,a.AdjustAmount,a.planAmount,a.BalanceAmount,a.AdjustedAmount,a.Remarks," & _
		  " CASE a.month WHEN 1 THEN ypp2c.PlanAmount1 + ypp2c.AdjustAmount1 " & _
		" WHEN 2 THEN ypp2c.PlanAmount2 + ypp2c.AdjustAmount2 " & _
		" WHEN 3 THEN ypp2c.PlanAmount3 + ypp2c.AdjustAmount3 " & _
		" WHEN 4 THEN ypp2c.PlanAmount4 + ypp2c.AdjustAmount4 " & _
		" WHEN 5 THEN ypp2c.PlanAmount5 + ypp2c.AdjustAmount5 " & _
		" WHEN 6 THEN ypp2c.PlanAmount6 + ypp2c.AdjustAmount6 " & _
		" WHEN 7 THEN ypp2c.PlanAmount7 + ypp2c.AdjustAmount7 " & _
		" WHEN 8 THEN ypp2c.PlanAmount8 + ypp2c.AdjustAmount8 " & _
		" WHEN 9 THEN ypp2c.PlanAmount9 + ypp2c.AdjustAmount9 " & _
		" WHEN 10 THEN ypp2c.PlanAmount10 + ypp2c.AdjustAmount10 " & _
		" WHEN 11 THEN ypp2c.PlanAmount11 + ypp2c.AdjustAmount11 " & _
		" WHEN 12 THEN ypp2c.PlanAmount12 + ypp2c.AdjustAmount12 END AS ssPlanAmount, " & _
		  "CASE a.month WHEN 1 THEN ypp2c.FactAmount1  " & _
		" WHEN 2 THEN ypp2c.FactAmount2 " & _
		" WHEN 3 THEN ypp2c.FactAmount3 " & _
		" WHEN 4 THEN ypp2c.FactAmount4 " & _
		" WHEN 5 THEN ypp2c.FactAmount5 " & _
		" WHEN 6 THEN ypp2c.FactAmount6 " & _
		" WHEN 7 THEN ypp2c.FactAmount7 " & _
		" WHEN 8 THEN ypp2c.FactAmount8 " & _
		" WHEN 9 THEN ypp2c.FactAmount9 " & _
		" WHEN 10 THEN ypp2c.FactAmount10 " & _
		" WHEN 11 THEN ypp2c.FactAmount11 " & _
		" WHEN 12 THEN ypp2c.FactAmount12 END AS ssFactAmount " & _
		 " FROM ys_YearPlanProceeding2Cost ypp2c " & _
		 " LEFT JOIN ys_DeptAdjustDtl a ON a.ProceedingGUID= ypp2c.ProceedingGUID AND a.CostGUID=ypp2c.CostGUID AND a.Year=ypp2c.Year) temp" & _
		" LEFT JOIN ys_DeptCost b ON temp.CostGUID=b.DeptCostGUID" & _
		" LEFT JOIN (select ProceedingGUID,ProceedingName from vys_Proceeding where IsDisabled=0 and (DeptGUID = '" & strDeptGUID & "') and ApproveState = '已审核' and vys_Proceeding.Year = " & nYear & " union all select '" & strDeptGUID & "' as ProceedingGUID,'日常费用' as ProceedingName) c ON temp.ProceedingGUID=c.ProceedingGUID " & _
		  " WHERE temp.DeptAdjustGUID = '" & strDeptAdjustGUID & "'" & _
		  " ORDER BY temp.Year,temp.Month "

		Return MyDB.GetDataTable(strSQL)
	End Function

	Private Function GetUpdateDeptAdjustDtlSQL(ByVal strDeptAdjustDtlGUID As String) As String
		Return "UPDATE ys_DeptAdjustDtl SET PlanAmount = 0, BalanceAmount = 0,AdjustedAmount = 0 WHERE DeptAdjustGUID='" & strDeptAdjustDtlGUID & "';"
	End Function
End Class
